package com.zzh.bc.db;

import com.zzh.bc.annotations.HTable;
import com.zzh.bc.log.log;
import com.zzh.bc.model.KeyTypeModel;
import org.apache.commons.lang.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;

public class MySQLExecute {

    /**
     * 更新语句
     *
     * @param sql 执行的SQL
     * @param obj 入参
     * @return 执行的结果
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object... obj) throws SQLException {
        PreparedStatement ps;
        Connection connection = C3p0Utils.getConnection();
        if (obj == null || obj.length == 0) {
            ps = connection.prepareStatement(sql);
        } else {
            ps = connection.prepareStatement(sql);
            setPsSQLParams(ps, obj);

        }
        int result = ps.executeUpdate();
        ps.close();
        C3p0Utils.releaseConnection(connection);
        return result;
    }

    /**
     * 设置参数
     *
     * @param ps  预执行语句
     * @param obj 参数
     * @throws SQLException 抛出异常
     */
    private static void setPsSQLParams(PreparedStatement ps, Object[] obj) throws SQLException {
        for (int i = 0; i < obj.length; i++) {
            if (obj[i] instanceof Integer) {
                ps.setInt(i + 1, (Integer) obj[i]);
            } else if (obj[i] instanceof Long) {
                ps.setLong(i + 1, (Long) obj[i]);
            } else if (obj[i] instanceof Float) {
                ps.setFloat(i + 1, (Float) obj[i]);
            } else if (obj[i] instanceof Double) {
                ps.setDouble(i + 1, (Double) obj[i]);
            } else if (obj[i] instanceof String) {
                ps.setString(i + 1, (String) obj[i]);
            } else {
                ps.setObject(i + 1, obj[i + 1]);
            }
        }
    }

    /**
     * 查询，返回数据集合
     *
     * @param sql 执行的SQL
     * @param obj 入参
     * @return 执行结果
     * @throws SQLException
     */
    public static ResultSet executeQuery(String sql, Object... obj) throws SQLException {
        Connection connection = C3p0Utils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        if (obj != null) {
            setPsSQLParams(ps, obj);
        }
        ResultSet resultSet = ps.executeQuery();
        System.out.println("-----SQL: " + sql);
        System.out.println("-----SQL 参数: " + Arrays.toString(obj));
        return resultSet;
    }

    public static <T> Optional<ResultSet> findObjById(Class<T> clz, String where, Object... obj) throws SQLException {
        try {
            Connection connection = C3p0Utils.getConnection();
            PreparedStatement ps = connection.prepareStatement(buildSqlInfo(clz, where));
            if (obj != null) {
                setPsSQLParams(ps, obj);
            }
            ResultSet resultSet = ps.executeQuery();
            return Optional.of(resultSet);
        } catch (SQLException ex) {
            return Optional.empty();
        }
    }

    public static <T> Optional<ResultSet> findAll(Class<T> clz, String where, Object... obj) throws SQLException {
        try {
            log.d("MySQLExecute.findAll clz " + clz.getCanonicalName() + ", where: " + where + ", obj: " + Arrays.toString(obj));
            Connection connection = C3p0Utils.getConnection();
            PreparedStatement ps = connection.prepareStatement(buildSqlInfo(clz, where));
            if (obj != null) {
                setPsSQLParams(ps, obj);
            }
            ResultSet resultSet = ps.executeQuery();
            return Optional.of(resultSet);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return Optional.empty();
        }
    }

    /**
     * @param clz
     * @param where
     * @param <T>
     * @return
     */
    private static <T> String buildSqlInfo(Class<T> clz, String where) {
        List<KeyTypeModel> keyTypeMapper = RSUtils.getKeyTypeMapper(clz);
        HTable annotation = clz.getAnnotation(HTable.class);
        log.d("MySQLExecute.buildSqlInfo: " + annotation.name());
        return buildSQLInfoSelect(annotation.name(), keyTypeMapper, where);
    }

    /**
     * @param name
     * @param keyTypeMapper
     * @param where
     * @return
     */
    private static String buildSQLInfoSelect(String name, List<KeyTypeModel> keyTypeMapper, String where) {
        log.d("组合的SQL入口 name: " + name + " where: " + where);
        StringBuilder sqlBuilder = new StringBuilder("SELECT ");

        if (!keyTypeMapper.isEmpty()) {
            String join = StringUtils.join(keyTypeMapper, ", ");
            sqlBuilder.append(join);
        } else {
            sqlBuilder.append(" * ");
        }
        sqlBuilder.append(" FROM ").append(name).append(" ").append(where);

        String string = sqlBuilder.toString();
        log.d("组合的SQL:" + string);
        return string;
    }


    /**
     * 保存
     *
     * @param sql 执行的语句
     * @param obj 参数
     * @return 返回影响的行数
     */
    public static int insert(String sql, Object... obj) throws SQLException {
        return extSQL(sql, obj);
    }

    private static int extSQL(String sql, Object[] obj) throws SQLException {
        Connection connection = C3p0Utils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        if (obj != null || obj.length > 0) {
            setPsSQLParams(ps, obj);
        }
        int i = ps.executeUpdate();
        C3p0Utils.releaseConnection(connection);
        ps.close();
        return i;
    }

    /**
     * 保存
     *
     * @param sql 执行的语句
     * @param obj 参数
     * @return 返回影响的行数
     */
    public static int delete(String sql, Object... obj) throws SQLException {
        return extSQL(sql, obj);
    }

    public static void beginTransaction() throws SQLException {
        C3p0Utils.beginTransaction();
    }

    public static void commitTransaction() throws SQLException {
        C3p0Utils.commitTransaction();
    }
}
